﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL
{
    public class SectionEmpInformtbDAO
    {
        //将通知保存到数据库记录
        public int saveInform(string sendUserLoginName, string inceptUserLoginName, string informContent, int informLevel, DateTime sendDateTime)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "insert into SectionEmpInformtb values(@sendUserLoginName, @inceptUserLoginName, @informContent, @informLevel, @SendDateTime, default)";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@sendUserLoginName", sendUserLoginName));
                sqlCmd.Parameters.Add(new SqlParameter("@inceptUserLoginName", inceptUserLoginName));
                sqlCmd.Parameters.Add(new SqlParameter("@informContent", informContent));
                sqlCmd.Parameters.Add(new SqlParameter("@informLevel", informLevel));
                sqlCmd.Parameters.Add(new SqlParameter("@SendDateTime", sendDateTime));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        //查询当前用户的收发通知
        public List<SectionEmpInformtb> findAllInform(string userLoginName, string type)
        {
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            List<SectionEmpInformtb> list = null;
            UserDAO userDAO = null;
            string sql = "";
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                if ("incept".Equals(type.Trim()))
                {
                    sql = "select * from SectionEmpInformtb where InceptUserLoginName = @UserLoginName order by SendDateTime desc";
                }
                else
                {
                    sql = "select * from SectionEmpInformtb where SendUserLoginName = @UserLoginName order by SendDateTime desc";
                }
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@UserLoginName", userLoginName));
                sqlDr = sqlCmd.ExecuteReader();
                list = new List<SectionEmpInformtb>();
                userDAO = new UserDAO();
                while (sqlDr.Read())
                {
                    SectionEmpInformtb sectionEmpInform = new SectionEmpInformtb();
                    sectionEmpInform.EmpInformId = (int)sqlDr["EmpInformId"];
                    sectionEmpInform.InceptUser = userDAO.FindByLoginName(sqlDr["InceptUserLoginName"].ToString());
                    sectionEmpInform.SendUser = userDAO.FindByLoginName(sqlDr["SendUserLoginName"].ToString());
                    sectionEmpInform.InformContent = sqlDr["InformContent"].ToString();
                    sectionEmpInform.InformLevel = (int)sqlDr["InformLevel"];
                    sectionEmpInform.SendDateTime = (DateTime)sqlDr["SendDateTime"];
                    sectionEmpInform.ReadState = (int)sqlDr["ReadState"];
                    list.Add(sectionEmpInform);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if(sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        //根据通知编号删除通知记录
        public int delInformByInformId(int inid)
        {
            int count = 0;
            SqlConnection sqlCon = null;
            try
            {
                sqlCon = SQLManager.GetConn();
                sqlCon.Open();
                string sql = "delete from SectionEmpInformtb where EmpInformId = @inid";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlCon);
                sqlCmd.Parameters.Add(new SqlParameter("@inid", inid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlCon != null && sqlCon.State == ConnectionState.Open)
                {
                    sqlCon.Close();
                    sqlCon.Dispose();
                }
            }
            return count;
        }

        //根据通知编号查询通知具体内容
        public SectionEmpInformtb findByInformId(int inid)
        {
            SqlConnection sqlCon = null;
            SqlDataReader sqlDr = null;
            SectionEmpInformtb sectionEmpInform = null;
            try
            {
                sqlCon = SQLManager.GetConn();
                sqlCon.Open();
                string sql = "select * from SectionEmpInformtb where EmpInformId = @inid";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlCon);
                sqlCmd.Parameters.Add(new SqlParameter("@inid", inid));
                sqlDr = sqlCmd.ExecuteReader();
                sqlDr.Read();
                UserDAO userDAO = new UserDAO();
                sectionEmpInform = new SectionEmpInformtb();
                sectionEmpInform.EmpInformId = (int)sqlDr["EmpInformId"];
                sectionEmpInform.InceptUser = userDAO.FindByLoginName(sqlDr["InceptUserLoginName"].ToString());
                sectionEmpInform.SendUser = userDAO.FindByLoginName(sqlDr["SendUserLoginName"].ToString());
                sectionEmpInform.InformContent = sqlDr["InformContent"].ToString();
                sectionEmpInform.InformLevel = (int)sqlDr["InformLevel"];
                sectionEmpInform.SendDateTime = (DateTime)sqlDr["SendDateTime"];
                sectionEmpInform.ReadState = (int)sqlDr["ReadState"];
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlCon != null && sqlCon.State == ConnectionState.Open)
                {
                    sqlCon.Close();
                    sqlCon.Dispose();
                }
            }
            return sectionEmpInform;
        }

        //根据通知编号更改通知的阅读状态
        public int updateInformReadState(int inid)
        {
            SqlConnection sqlCon = null;
            int count = 0;
            try
            {
                sqlCon = SQLManager.GetConn();
                sqlCon.Open();
                string sql = "update SectionEmpInformtb set ReadState = 1 where EmpInformId = @inid";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlCon);
                sqlCmd.Parameters.Add(new SqlParameter("@inid", inid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlCon != null && sqlCon.State == ConnectionState.Open)
                {
                    sqlCon.Close();
                    sqlCon.Dispose();
                }
            }
            return count;
        }
    }
}
